package SQL;

import java.sql.*;
import java.util.Scanner;

/**
 * Created with IntelliJ IDEA.
 * Description:
 * Author: 保护小周
 * Date: 2022-11-06
 * Time: 12:56
 */
public class Student implements Configuration {

    private static void field() { //字段
        System.out.println("学号      " + "姓名  "+ "年龄 " + "是否返校 " + "联系方式");
    }

    //显示学生基本表
    public static void show() {
        // 加载驱动
        try {
            Class.forName(dirver);
            System.out.println("驱动加载成功！");
           // 链接数据库,获得链接对象

            Connection connection = DriverManager.getConnection(
                     url ,username, password);
            System.out.println("数据库连接成功！");

           // 通过连接对象，创建执行环境
            Statement statement = connection.createStatement();

            // 执行SQL 语句 得到结果集
            ResultSet resultSet = statement.executeQuery("select * from student"); //sql 查询语句

            field(); //字段

            while (resultSet.next()) {
                String id = resultSet.getString("id");
                String name = resultSet.getString("name");
                int age =resultSet.getInt("age");
                String reason = resultSet.getString("reason");
                String call = resultSet.getString("cell_Phone_Number");
                System.out.print(id + " ");
                System.out.print(name + " ");
                System.out.print(age + " ");
                System.out.print(reason + " ");
                System.out.print(call + " ");
                System.out.println();
            }

        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("驱动加载失败！");
            System.out.println("数据库连接失败！");
            System.out.println("查询失败！");
        }

    }

    //添加数据
    public static void addStudent() throws ClassNotFoundException, SQLException {
        //注册jdbc驱动
        Class.forName(dirver);

        Scanner in = new Scanner(System.in);

        System.out.println("请输入学生id:");
        String id = in.nextLine();

        System.out.println("请输入学生姓名:");
        String name = in.nextLine();

        System.out.println("请输入年龄：");
        int age = in.nextInt();

        in.nextLine();
        System.out.println("请输入是否返校：（例：已返校-未返校）");
        String reason1 = in.nextLine();

        System.out.println("请输入电话号码：");
        String cell = in.nextLine();

        //打开链接--连接mysql
        Connection connection = DriverManager.getConnection(url, username, password);
        Statement statement = connection.createStatement();

        String sql = "INSERT INTO student values("+id+",'"+name+"',"+age+", '"+ reason1 +"', '"+cell+"')";
        //执行sql语句
        statement.executeUpdate(sql);

        System.out.println("添加成功");
        connection.close();
    }

    //删除数据
    public static void delStudent() throws ClassNotFoundException, SQLException {

        //注册jdbc驱动
        Class.forName(dirver);
        System.out.println("连接数据库中……");

        Scanner in = new Scanner(System.in);
        System.out.println("请输入要删除学生的id");
        String delId = in.nextLine();

        //打开链接
        Connection connection = DriverManager.getConnection(url, username, password);
        Statement statement = connection.createStatement();

        String sql = "delete from student where id = " +delId+"";

        statement.executeUpdate(sql);

        System.out.println("删除成功!");
        connection.close();
    }

    //查询某学生基本信息表
    public static void find() throws ClassNotFoundException, SQLException{

        //注册jdbc驱动
        Class.forName(dirver);
        //打开链接
        Connection connection = DriverManager.getConnection(url,username, password);
        Statement statement = connection.createStatement();

        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入要查询的ID");
        int fid = scanner.nextInt();
        //定义sql语句
        String sql = "SELECT * FROM student where id =" + fid +"";

        //statement.executeUpdate(sql);
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            String id = resultSet.getString("id");
            String name = resultSet.getString("name");
            int age =resultSet.getInt("age");
            String reason = resultSet.getString("reason");
            String call = resultSet.getString("cell_Phone_Number");
            System.out.print(id + " ");
            System.out.print(name + " ");
            System.out.print(age + " ");
            System.out.print(reason + " ");
            System.out.print(call + " ");
            System.out.println();
        }
    }

    public static void find(String fid) throws ClassNotFoundException, SQLException{

        //注册jdbc驱动
        Class.forName(dirver);
        //打开链接
        Connection connection = DriverManager.getConnection(url,username, password);
        Statement statement = connection.createStatement();

        //定义sql语句
        String sql = "SELECT * FROM student where id =" + fid +"";

        //statement.executeUpdate(sql);
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            String id = resultSet.getString("id");
            String name = resultSet.getString("name");
            int age =resultSet.getInt("age");
            String reason = resultSet.getString("reason");
            String call = resultSet.getString("cell_Phone_Number");
            System.out.print(id + " ");
            System.out.print(name + " ");
            System.out.print(age + " ");
            System.out.print(reason + " ");
            System.out.print(call + " ");
            System.out.println();
        }
    }

    private static void menu() {
        System.out.println("------------修改学生信息界面------------");
        System.out.println("**************************************");
        System.out.println("***** 1-修改学生id   2-修改学生姓名 *****");
        System.out.println("***** 3-修改学生年龄  4-返校情况    *****");
        System.out.println("***** 5-修改联系方式  6-退出此界面  *****");
        System.out.println("**************************************");

        System.out.print("请选择修改内容：");
    }

    //修改数据
    public static void modify() throws ClassNotFoundException, SQLException{

        Scanner in = new Scanner(System.in);

        String sql = null;
        boolean flag = true;
        //String sql = "UPDATE student SET id = "+id2+",name = '"+name2+"' WHERE id = "+id1+";";
        System.out.println("请输入要修改的学生id:");
        String id1 = in.nextLine();

        find(id1); //查询该学生的学生信息

        //注册jdbc驱动
        Class.forName(dirver);
        //打开链接
        Connection connection = DriverManager.getConnection(url,username, password);
        Statement statement = connection.createStatement();
        System.out.println("连接成功！");


        while(flag) {
            menu(); //菜单
            int size = in.nextInt();
            in.nextLine();

            switch (size) {
                case 1:
                    System.out.println("请输出修改后的id");
                    String id2 = in.nextLine();
                    sql = "UPDATE student SET id = '"+id2+"'  WHERE id = "+id1+";";
                    //执行sql语句
                    statement.executeUpdate(sql);
                    System.out.println("修改成功");
                    break;
                case 2:
                    System.out.println("请输入修改后的学生姓名:");
                    String name1 = in.nextLine();
                    sql = "UPDATE student SET name ='"+name1+"' WHERE id = "+id1+";";
                    statement.executeUpdate(sql);
                    System.out.println("修改成功");
                    break;
                case 3:
                    System.out.println("请输入修改后学生年龄:");
                    int age = in.nextInt();

                    sql = "UPDATE student SET age ="+age+" WHERE id = "+id1+";";
                    statement.executeUpdate(sql);
                    System.out.println("修改成功");
                    break;
                case 4:
                    System.out.println("请输入修改学生返校信息:");
                    String reason1 = in.nextLine();
                    sql = "UPDATE student SET reason ='"+reason1+"' WHERE id = "+id1+";";
                    statement.executeUpdate(sql);
                    System.out.println("修改成功");
                    //System.out.println("无权限修改,请联系管理员处理");
                    break;
                case 5:
                    System.out.println("请输入要修改的学生的联系方式:");
                    String cell  = in.nextLine();
                    sql = "UPDATE student SET cell_Phone_Number ="+cell+" WHERE id = "+id1+";";
                    statement.executeUpdate(sql);
                    System.out.println("修改成功");
                    break;
                case 6:
                    flag = false;  connection.close();
                    break;
                default:
                    System.out.println("输入错误！");
            }
        }
    }
}
